﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Net.Mail;
using System.Text;
using System.Threading.Tasks;

namespace SendEmailCustomerService
{
    public static class MainCore
    {
        public static log4net.ILog logger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        public static string connectstring = ConfigurationSettings.AppSettings["DefaultConnection"];


        public static void MainFunctionSendEmail()
        {
            var recipient = ConfigurationSettings.AppSettings["ContactSupportEmail"];
            var bcc = ConfigurationSettings.AppSettings["BccContactSupportEmail"];

            var ds = dta_GetListCustomer();
            logger.Info("Number of customers : " + ds.Tables[0].Rows.Count);
            logger.Info("Number of customers duedate : " + ds.Tables[1].Rows.Count);

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                SendContactEmail(new SendContactCommand()
                {
                    Email = row["Email"].ToString(),
                    Message = "",
                    Name = row["Name"].ToString(),
                    DueDate = Convert.ToDateTime(row["NextDueDate"]),
                    CustomerId = Convert.ToInt32(row["CustomerId"].ToString()),
                    Recipient = recipient,
                    BccRecipient = bcc
                });
            }

            foreach (DataRow row in ds.Tables[1].Rows)
            {
                UpdateCustomerDueDate(Convert.ToInt32(row["CustomerId"].ToString()), Convert.ToDateTime(row["NextDueDate"]));
            }

        }

        public static DataSet dta_GetListCustomer()
        {
            var sqlConn = new SqlConnection(connectstring);
            var sqlCmd = new SqlCommand();
            try
            {
                sqlConn.Open();
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = "GetCustomers";
                sqlCmd.Connection = sqlConn;
                var adapter = new SqlDataAdapter(sqlCmd);
                var ds = new DataSet();
                (new DynamicallyNameTableAdapter()).FillFromReader(ds, adapter.SelectCommand);
                if (ds.Tables.Count > 0)
                {
                    return ds;
                }
            }
            catch (Exception ex)
            {
                logger.Error("Open Connection Error : " + ex.Message);
            }
            finally
            {
                sqlCmd.Dispose();
                sqlConn.Close();
                sqlConn.Dispose();
            }
            return null;
        }

        public static bool UpdateSendMail(int custId, DateTime mailDatetime)
        {
            var connectionString = ConfigurationSettings.AppSettings["DefaultConnection"];
            try
            {
                using (var con = new SqlConnection(connectionString))
                {
                    using (var cmd = new SqlCommand("[UpdateSendMailCustomer]", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = custId;
                        cmd.Parameters.Add("@NextDueDate", SqlDbType.DateTime).Value = mailDatetime;
                        con.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool UpdateCustomerDueDate(int custId, DateTime mailDatetime)
        {
            var connectionString = ConfigurationSettings.AppSettings["DefaultConnection"];
            try
            {
                using (var con = new SqlConnection(connectionString))
                {
                    using (var cmd = new SqlCommand("[UpdateCustomer]", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = custId;
                        cmd.Parameters.Add("@NextDueDate", SqlDbType.DateTime).Value = mailDatetime;
                        con.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static void SendContactEmail(SendContactCommand command)
        {
            var email = BuildContactEmail(command);
            SendEmail(email);
            UpdateSendMail(command.CustomerId, command.DueDate);
        }

        public static void SendEmail(MailMessage mailMessage, bool isBodyHtml = true)
        {
            if (mailMessage.To.Count < 1)
            { }
            //throw new ValidationException("MailMessage object must contain at least an address where you want to send the message.");
            mailMessage.IsBodyHtml = isBodyHtml;
            var client = new SmtpClient();
            client.Send(mailMessage);
        }

        private static MailMessage BuildContactEmail(SendContactCommand command)
        {
            var mailBody = TranslateMergeField(Resources.Content.Body, command.Name, command.Email, command.CustomerId, command.DueDate);
            var mailSubject = TranslateMergeField(Resources.Content.Header, command.Name, command.Email, command.CustomerId, command.DueDate);

            var email = new MailMessage();
            email.To.Add(new MailAddress(command.Email));
            //if (!string.IsNullOrWhiteSpace(bcc))
            //{
            //    email.Bcc.Add(bcc);
            //}
            email.Subject = mailSubject;
            email.Body = mailBody;
            email.IsBodyHtml = true;
            return email;
        }

        private static string TranslateMergeField(string body, string name, string email, int custId, DateTime mailDatetime)
        {
            var result = body.Replace("{CustomerName}", name)
                .Replace("{DueDate}", mailDatetime.ToShortDateString())
                ;
            return result;
        }
    }
}

public sealed class DynamicallyNameTableAdapter : DataAdapter
{
    public int FillFromReader(DataSet set, DbCommand command)
    {
        int count = 0;
        using (DbDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.KeyInfo))
        {
            do
            {
                DataTable table = null;
                string tableName = (reader.GetSchemaTable().Rows[0][SchemaTableColumn.BaseTableName] as string);
                if (!String.IsNullOrEmpty(tableName))
                {
                    table = set.Tables[tableName];
                }
                if (null == table)
                {
                    table = new DataTable(tableName);
                    set.Tables.Add(table);
                }

                count += Fill(new DataTable[] { table }, reader, 0, 0);
            } while (reader.NextResult());
        }
        return count;
    }
}
